In [1]:
import pandas as pd
import seaborn as sns
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.colors
In [2]:
df = pd.read_csv('log.csv', parse_dates=['Joined', 'Left', 'Time'])
df['Time'] = df['Left'] - df['Joined']
df = df[df['Member Name'] != 'Sauron']
df
Out[2]:
Member Id Member Name Joined Left Time
0 210743674525450240 stratos1805 2021-04-27 02:21:53.125370 2021-04-27 02:33:41.455374 0 days 00:11:48.330004
1 132415133711466496 סדאם חוסיין ז"ל 2021-04-27 02:21:53.125370 2021-04-27 02:35:55.345365 0 days 00:14:02.219995
2 192642893159202816 MrStormagedon 2021-04-27 02:21:53.125370 2021-04-27 03:10:15.058915 0 days 00:48:21.933545
3 193006567455457280 Cardi Biton 2021-04-27 12:44:58.546623 2021-04-27 12:56:30.695541 0 days 00:11:32.148918
4 192986627998613504 rone 2021-04-27 13:55:12.166887 2021-04-27 14:14:01.761058 0 days 00:18:49.594171
... ... ... ... ... ...
442 192660675733094401 יניבצ'וק 2021-05-21 17:24:27.238967 2021-05-21 18:19:34.844232 0 days 00:55:07.605265
443 192986627998613504 rone 2021-05-21 17:23:58.069672 2021-05-21 18:40:43.942043 0 days 01:16:45.872371
444 208115226489389058 XPEZNAZ 2021-05-21 17:12:36.064512 2021-05-21 18:40:45.969189 0 days 01:28:09.904677
445 192660675733094401 יניבצ'וק 2021-05-21 18:34:38.815753 2021-05-21 18:41:39.365194 0 days 00:07:00.549441
446 193006567455457280 Cardi Biton 2021-05-21 17:27:52.742312 2021-05-21 18:41:51.428712 0 days 01:13:58.686400

445 rows × 5 columns

In [3]:
min_datetime = df['Joined'].min()
max_datetime = df['Left'].max()
min_date, max_date = min_datetime.date(), max_datetime.date()
df = df.groupby('Member Name').filter(lambda x: x['Time'].sum().seconds / 3600 > 5)
unique_members = df['Member Name'].unique().tolist()

colormap = plt.cm.tab20(np.linspace(0, 1, len(unique_members)))
palette = [matplotlib.colors.to_hex(c) for c in colormap]

print(f'Data collected over {max_datetime - min_datetime} with {len(unique_members)} unique members')
Data collected over 24 days 16:19:58.303342 with 11 unique members
In [4]:
# sessions.index = pd.CategoricalIndex(sessions.index, unique_members)
# sessions = sessions.sort_values(ascending=False)
# sessions
# sessions.plot(use_index=True, kind='box', figsize=(20, 5), title='Average Session Length', xlabel='', ylabel='Hours', rot=0, color=colormap)
# plt.show()
plt.figure(figsize=(20, 5))
sns.boxplot(x='Member Name', y=df['Time'].dt.seconds / 3600, data=df, palette=palette)
plt.xlabel('')
plt.ylabel('Hours')
plt.show()
2021-05-21T18:54:12.805234 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [5]:
start = min_datetime.replace(minute=0, second=0, microsecond=0)
end = max_datetime.replace(minute=0, second=0, microsecond=0) + pd.offsets.Hour(1)
attendance_data = []
date = start
while date < end:
    members = df[(df['Joined'] <= date + pd.offsets.Minute(1)) & (df['Left'] >= date)]['Member Name'].to_list()
    attendance_data.append([date] + [member in members for member in unique_members])
    date += pd.offsets.Minute(1)
att_df = pd.DataFrame(attendance_data, columns=['Date'] + unique_members)
In [6]:
axes = att_df.set_index('Date').rolling(1).mean().plot(figsize=(14, 24), yticks=[0, 1], subplots=True, sharex=True, kind='area', color=colormap)
for ax in axes:
    ax.set_yticklabels(['Disconnected', 'Connected'])
plt.subplots_adjust()
plt.show()
2021-05-21T18:54:40.063362 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [7]:
def part_of_day(date):
    if 0 <= date.hour < 6:
        return '00:00 - 05:59'
    elif 6 <= date.hour < 12:
        return '06:00 - 11:59'
    elif 12 <= date.hour < 18:
        return '12:00 - 17:59'
    else:
        return '18:00 - 23:59'

weekday_order = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']

daily_att = att_df.groupby(att_df['Date'].dt.day_name()).sum() / 60
daily_att.index = pd.CategoricalIndex(daily_att.index, weekday_order)
daily_att = daily_att.sort_index()
hourly_att = att_df.groupby(att_df['Date'].apply(part_of_day)).sum() / 60
all_att = att_df.sum() / 60

daily_att.plot(use_index=True, kind='bar', figsize=(20, 5), title='Daily Attendance', xlabel='Weekday', rot=0, color=colormap)
plt.show()
hourly_att.plot(use_index=True, kind='bar', figsize=(20, 5), title='Hourly Attendance', xlabel='Time', rot=0, color=colormap)
plt.show()
all_att.sort_values(ascending=False).plot(use_index=True, kind='bar', figsize=(20, 5), title='Total Attendance', xlabel=None, ylabel='Hours', rot=0, color=colormap)
plt.show()
2021-05-21T18:54:45.329913 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
2021-05-21T18:54:45.967717 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
2021-05-21T18:54:46.326773 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [8]:
corr = att_df.corr()
corr
Out[8]:
stratos1805 סדאם חוסיין ז"ל MrStormagedon Cardi Biton rone October Detka NightSeeker Groovy xWarLord_Sharon OMGitsyuvalT
stratos1805 1.000000 0.523870 0.321591 0.315183 0.125316 0.340838 0.101872 0.286916 0.109880 0.008356 0.128848
סדאם חוסיין ז"ל 0.523870 1.000000 0.434469 0.370018 0.188720 0.420788 0.199126 0.349970 0.322072 0.031768 0.135059
MrStormagedon 0.321591 0.434469 1.000000 0.213044 0.115126 0.375422 0.266700 0.236414 0.161277 -0.010249 0.028885
Cardi Biton 0.315183 0.370018 0.213044 1.000000 0.146321 0.312288 0.085559 0.185557 0.064618 -0.003219 0.180320
rone 0.125316 0.188720 0.115126 0.146321 1.000000 0.190927 -0.016444 0.121388 0.107092 0.354917 0.076531
October 0.340838 0.420788 0.375422 0.312288 0.190927 1.000000 -0.025301 0.143012 0.229707 0.016094 0.007716
Detka 0.101872 0.199126 0.266700 0.085559 -0.016444 -0.025301 1.000000 0.258357 0.040730 -0.018352 -0.022963
NightSeeker 0.286916 0.349970 0.236414 0.185557 0.121388 0.143012 0.258357 1.000000 0.105355 -0.021400 0.023600
Groovy 0.109880 0.322072 0.161277 0.064618 0.107092 0.229707 0.040730 0.105355 1.000000 -0.017568 0.030341
xWarLord_Sharon 0.008356 0.031768 -0.010249 -0.003219 0.354917 0.016094 -0.018352 -0.021400 -0.017568 1.000000 -0.010998
OMGitsyuvalT 0.128848 0.135059 0.028885 0.180320 0.076531 0.007716 -0.022963 0.023600 0.030341 -0.010998 1.000000
In [9]:
plt.figure(figsize=(20, 20))
sns.heatmap(corr, cmap='YlGnBu')
plt.xticks(rotation=0)
plt.show()
2021-05-21T18:54:46.728698 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [10]:
plt.figure(figsize=(20, 20))
sns.heatmap(corr[(abs(corr) > 0.3)], cmap='YlGnBu')
plt.xticks(rotation=0)
plt.show()
2021-05-21T18:54:47.195000 image/svg+xml Matplotlib v3.4.1, https://matplotlib.org/
In [11]:
pd.DataFrame(corr.unstack().sort_values().drop_duplicates())
Out[11]:
0
October Detka -0.025301
OMGitsyuvalT Detka -0.022963
NightSeeker xWarLord_Sharon -0.021400
Detka xWarLord_Sharon -0.018352
Groovy xWarLord_Sharon -0.017568
rone Detka -0.016444
OMGitsyuvalT xWarLord_Sharon -0.010998
xWarLord_Sharon MrStormagedon -0.010249
Cardi Biton -0.003219
OMGitsyuvalT October 0.007716
stratos1805 xWarLord_Sharon 0.008356
xWarLord_Sharon October 0.016094
NightSeeker OMGitsyuvalT 0.023600
OMGitsyuvalT MrStormagedon 0.028885
Groovy OMGitsyuvalT 0.030341
xWarLord_Sharon סדאם חוסיין ז"ל 0.031768
Groovy Detka 0.040730
Cardi Biton Groovy 0.064618
rone OMGitsyuvalT 0.076531
Cardi Biton Detka 0.085559
Detka stratos1805 0.101872
NightSeeker Groovy 0.105355
Groovy rone 0.107092
stratos1805 0.109880
rone MrStormagedon 0.115126
NightSeeker rone 0.121388
stratos1805 rone 0.125316
OMGitsyuvalT stratos1805 0.128848
סדאם חוסיין ז"ל OMGitsyuvalT 0.135059
NightSeeker October 0.143012
Cardi Biton rone 0.146321
MrStormagedon Groovy 0.161277
Cardi Biton OMGitsyuvalT 0.180320
NightSeeker 0.185557
rone סדאם חוסיין ז"ל 0.188720
October rone 0.190927
Detka סדאם חוסיין ז"ל 0.199126
MrStormagedon Cardi Biton 0.213044
Groovy October 0.229707
NightSeeker MrStormagedon 0.236414
Detka NightSeeker 0.258357
MrStormagedon Detka 0.266700
stratos1805 NightSeeker 0.286916
Cardi Biton October 0.312288
stratos1805 0.315183
stratos1805 MrStormagedon 0.321591
Groovy סדאם חוסיין ז"ל 0.322072
October stratos1805 0.340838
סדאם חוסיין ז"ל NightSeeker 0.349970
rone xWarLord_Sharon 0.354917
Cardi Biton סדאם חוסיין ז"ל 0.370018
October MrStormagedon 0.375422
סדאם חוסיין ז"ל 0.420788
MrStormagedon סדאם חוסיין ז"ל 0.434469
סדאם חוסיין ז"ל stratos1805 0.523870
stratos1805 stratos1805 1.000000